package com.hifo.warehouse.util.excel;

import jxl.read.biff.BiffException;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import java.io.*;
import java.util.ArrayList;
import java.util.List;

/**
 * Created by Administrator on 2018/5/14 0014.
 */
public class ExcelUtil {
    private static final String EXCEL_XLS = "xls";
    private static final String EXCEL_XLSX = "xlsx";
    
 // 去读Excel的方法readExcel，该方法的入口参数为一个File对象
    public static List readExcelIs(InputStream is) {
        try {
            // 创建输入流，读取Excel
            // InputStream is = new FileInputStream(file.getAbsolutePath());
            // jxl提供的Workbook类
        	
            jxl.Workbook wb = jxl.Workbook.getWorkbook(is);
            
            // Excel的页签数量
            int sheet_size = wb.getNumberOfSheets();
            for (int index = 0; index < sheet_size; index++) {
                List<List> outerList = new ArrayList<List>();
                // 每个页签创建一个Sheet对象
                jxl.Sheet sheet = wb.getSheet(index);
                // sheet.getRows()返回该页的总行数
                for (int i = 1; i < sheet.getRows(); i++) {
                    List innerList = new ArrayList();
                    // sheet.getColumns()返回该页的总列数
                    for (int j = 0; j < sheet.getColumns(); j++) {
                        String cellinfo = sheet.getCell(j, i).getContents();
                        innerList.add(cellinfo);
                    }
                    outerList.add(i-1, innerList);
                }
                return outerList;
            }
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (BiffException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return null;
    }
    
    
    // 去读Excel的方法readExcel，该方法的入口参数为一个File对象
    public static List readExcel(File file) {
        try {
            // 创建输入流，读取Excel
            InputStream is = new FileInputStream(file.getAbsolutePath());
            // jxl提供的Workbook类
            jxl.Workbook wb = jxl.Workbook.getWorkbook(is);
            // Excel的页签数量
            int sheet_size = wb.getNumberOfSheets();
            for (int index = 0; index < sheet_size; index++) {
                List<List> outerList = new ArrayList<List>();
                // 每个页签创建一个Sheet对象
                jxl.Sheet sheet = wb.getSheet(index);
                // sheet.getRows()返回该页的总行数
                for (int i = 0; i < sheet.getRows(); i++) {
                    List innerList = new ArrayList();
                    // sheet.getColumns()返回该页的总列数
                    for (int j = 0; j < sheet.getColumns(); j++) {
                        String cellinfo = sheet.getCell(j, i).getContents();
                        innerList.add(cellinfo);
//                        System.out.print(cellinfo);
                    }
                    outerList.add(i, innerList);
//                    System.out.println();
                }
                return outerList;
            }
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (BiffException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return null;
    }

    public static void writeExcel(List<List> dataList, String finalXlsxPath) {
        OutputStream out = null;
        try {
            // 读取Excel文档
            File finalXlsxFile = new File(finalXlsxPath);
            if (!finalXlsxFile.exists())
                finalXlsxFile.createNewFile();
            Workbook workBook = getWorkbok(finalXlsxFile);
            // sheet 对应一个工作页
            Sheet sheet = workBook.createSheet();
            /**
             * 往Excel中写新数据
             */
            List<String> rowData;
            Cell cell;
            Row row;
            for (int i = 0; i < dataList.size(); i++) {
                rowData = dataList.get(i);
                row = sheet.createRow(i);
                for (int j = 0; j < rowData.size(); j++) {
                    cell = row.createCell(j);
                    cell.setCellValue(String.valueOf(rowData.get(j)));
                }
            }
            // 创建文件输出流，准备输出电子表格：这个必须有，否则你在sheet上做的任何操作都不会有效
            out = new FileOutputStream(finalXlsxPath);
            workBook.write(out);
            workBook.close();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (out != null) {
                    out.flush();
                    out.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 判断Excel的版本,获取Workbook
     *
     * @return
     * @throws IOException
     */
    public static Workbook getWorkbok(File file) throws IOException {
        Workbook wb = null;
        FileInputStream in = new FileInputStream(file);
        if (file.getName().endsWith(EXCEL_XLS)) {     //Excel&nbsp;2003
            wb = new HSSFWorkbook(in);
        } else if (file.getName().endsWith(EXCEL_XLSX)) {    // Excel 2007/2010
            wb = new XSSFWorkbook(in);
        }
        return wb;
    }
    /**
     * 判断Excel的版本,获取Workbook
     *
     * @return
     * @throws IOException
     */
    public static Workbook getWorkbok(String str,InputStream in) throws IOException {
        Workbook wb = null;
        if (str.endsWith(EXCEL_XLS)) {     //Excel&nbsp;2003
            wb = new HSSFWorkbook(in);
        } else if (str.endsWith(EXCEL_XLSX)) {    // Excel 2007/2010
            wb = new XSSFWorkbook(in);
        }
        return wb;
    }
    
    
    /**
     * 判断Excel的版本,获取Workbook
     *
     * @return
     * @throws IOException
     */
    public static Workbook getWorkbok(MultipartFile file) throws IOException {
        Workbook wb = null;
        InputStream in = file.getInputStream();
        if (file.getName().endsWith(EXCEL_XLS)) {     //Excel&nbsp;2003
            wb = new HSSFWorkbook(in);
        } else if (file.getName().endsWith(EXCEL_XLSX)) {    // Excel 2007/2010
            wb = new XSSFWorkbook(in);
        }
        return wb;
    }
    //解析Excel
    
    public static List<List<String>> readExceled(MultipartFile file) throws IOException{
    	
    	Workbook wb = getWorkbok(file);
    	//开始解析
    	int numberSheets  = wb.getNumberOfSheets();
    	List<List<String>> outerList = new ArrayList<>();
    	for(int i=0;i<numberSheets;i++){
    		//读取sheet 
    		 Sheet sheet = wb.getSheetAt(i);
    		    int firstRowIndex = sheet.getFirstRowNum()+1;   //第一行是列名，所以不读
    	        int lastRowIndex = sheet.getLastRowNum();
    	        List<String> interList =null;
    	        for(int rIndex = firstRowIndex; rIndex <= lastRowIndex; rIndex++) {   //遍历行
    	            System.out.println("rIndex: " + rIndex);
    	            Row row = sheet.getRow(rIndex);
    	            if (row != null) {
    	            	interList = new ArrayList<>();
    	                int firstCellIndex = row.getFirstCellNum();
    	                int lastCellIndex = row.getLastCellNum();
    	                for (int cIndex = firstCellIndex; cIndex < lastCellIndex; cIndex++) {   //遍历列
    	                    Cell cell = row.getCell(cIndex);
    	                    if (cell != null) {
    	                        interList.add(cell.toString());
    	                    }
    	                }
    	            }
    	        }
    	        outerList.add(interList);
    	}
        
		return outerList;
    }
    
    /**
     * 写出文件
     * @param list
     */
    public synchronized static void  writeExcelFileString(String sheetName,List<List<String>> list,String url){

        if (sheetName==null||list==null) throw new NullPointerException();
        long time=System.currentTimeMillis();
        System.out.println("总数据条数------------>"+list.size());
        System.out.println("-----------------开始创建EXCEL文档---------------------");
        //创建excel工作簿
        XSSFWorkbook workbook1 = new XSSFWorkbook();
        SXSSFWorkbook sxssfWorkbook = new SXSSFWorkbook(workbook1, 100);
        CellStyle cs = sxssfWorkbook.createCellStyle();
        cs.setAlignment(HorizontalAlignment.CENTER);
        Font font = sxssfWorkbook.createFont();
        font.setColor(Font.COLOR_RED);
        cs.setFont(font);
        try {
            POIUtil poiUtil=new POIUtil(sxssfWorkbook);
            System.out.println("-----------------开始向EXCEL写入数据---------------------");
            poiUtil.writeString(list, sheetName, true);
            System.out.println("-----------------数据写入完毕开始保存---------------------");
            //创建一个文件 命名为workbook.xls
            FileOutputStream fileOut = new FileOutputStream(url);
            // 把上面创建的工作簿输出到文件中
            sxssfWorkbook.write(fileOut);
            //关闭输出流
            fileOut.close();
        } catch (IOException e) {
            e.printStackTrace();
        }

        System.out.println("-----------------导出完毕---------------------");
        System.out.println("整个导出过程用时--------->"+((System.currentTimeMillis()-time)/1000)+"秒");
    }
    
    
    /**
     * 写出文件
     * @param list
     */
    public synchronized static void  writeExcelFile(String sheetName,List<List<Object>> list,String url){

        if (sheetName==null||list==null) throw new NullPointerException();
        long time=System.currentTimeMillis();
        System.out.println("总数据条数------------>"+list.size());
        System.out.println("-----------------开始创建EXCEL文档---------------------");
        //创建excel工作簿
        XSSFWorkbook workbook1 = new XSSFWorkbook();
        SXSSFWorkbook sxssfWorkbook = new SXSSFWorkbook(workbook1, 100);
        CellStyle cs = sxssfWorkbook.createCellStyle();
        cs.setAlignment(HorizontalAlignment.CENTER);
        Font font = sxssfWorkbook.createFont();
        font.setColor(Font.COLOR_RED);
        cs.setFont(font);
        try {
            POIUtil poiUtil=new POIUtil(sxssfWorkbook);
            System.out.println("-----------------开始向EXCEL写入数据---------------------");
            poiUtil.write(list, sheetName, true);
            System.out.println("-----------------数据写入完毕开始保存---------------------");
            //创建一个文件 命名为workbook.xls
            FileOutputStream fileOut = new FileOutputStream(url);
            // 把上面创建的工作簿输出到文件中
            sxssfWorkbook.write(fileOut);
            //关闭输出流
            fileOut.close();
        } catch (IOException e) {
            e.printStackTrace();
        }

        System.out.println("-----------------导出完毕---------------------");
        System.out.println("整个导出过程用时--------->"+((System.currentTimeMillis()-time)/1000)+"秒");
    }
    
    
    /**
    * 写出文件
    * @param lists
    */
   public synchronized void  writeExcelFile1(String[] sheetName,List<List<Object>>[] lists,String url){

       if (sheetName==null||lists==null) throw new NullPointerException();

       long time=System.currentTimeMillis();
       System.out.println("-----------------开始创建EXCEL文档---------------------");

       //创建excel工作簿
       Workbook wb = new XSSFWorkbook();
       CellStyle cs = wb.createCellStyle();
       cs.setAlignment(HorizontalAlignment.CENTER);
       Font font = wb.createFont();
       font.setColor(Font.COLOR_RED);
       cs.setFont(font);

       try {
           POIUtil poiUtil=new POIUtil(wb);

           System.out.println("-----------------开始向EXCEL写入数据---------------------");
           for (int i=0;i<lists.length;i++) {
               System.out.println("-----------------共 ["+lists.length+"] 个文本文档开始写入第 ["+(i+1)+"] 个文档---------------------");
               poiUtil.write(lists[i], sheetName[i], true);
           }
           System.out.println("-----------------数据写入完毕开始保存---------------------");

           //创建一个文件 命名为workbook.xls
           FileOutputStream fileOut = new FileOutputStream(url);
           // 把上面创建的工作簿输出到文件中
           wb.write(fileOut);
           //关闭输出流
           fileOut.close();
       } catch (IOException e) {
           // TODO Auto-generated catch block
           e.printStackTrace();
       }

       System.out.println("-----------------导出完毕---------------------");
       System.out.println("整个导出过程用时--------->"+((System.currentTimeMillis()-time)/1000)+"秒");
   }

    
}
